package edu.bw.dao.impl;

import edu.bw.dao.DeptDao;
import edu.bw.dto.UpdateDeptForm;
import edu.bw.pojo.Dept;
import edu.bw.dto.SelectDeptByConditionForm;
import edu.bw.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/*SELECT d.id,d.dept_name AS deptName,d.tel,d.email,d.`desc`,COUNT(u.id) AS emps
        FROM bw_zzy_dept d LEFT JOIN bw_zzy_users u ON d.id = u.dept_id AND u.`status`=1
        WHERE 1=1
        AND d.dept_name LIKE "%后%"
        GROUP BY d.id
        LIMIT 0,10*/
/*SELECT COUNT(*) FROM
        (SELECT d.id,d.dept_name AS deptName,d.tel,d.email,d.`desc`,COUNT(u.id) AS emps
        FROM bw_zzy_dept d LEFT JOIN bw_zzy_users u ON d.id = u.dept_id AND u.`status`=1 #查询在职状态的员工
        WHERE 1=1
        #AND d.dept_name LIKE "%后%"
        GROUP BY d.id
        )AS temp*/
public class DeptDaoImpl implements DeptDao {
    QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
    @Override
    public List<Map<String, Object>> selectDeptByCondition(SelectDeptByConditionForm selectCondition) {
        StringBuilder sql=new StringBuilder("SELECT d.id,d.dept_name AS deptName,d.tel,d.email,d.`desc`,COUNT(u.id) AS emps" +
                " FROM bw_zzy_dept d LEFT JOIN bw_zzy_users u ON d.id = u.dept_id AND u.`status`=1 " +
                " WHERE 1=1 ");

        List<Object> patten = new ArrayList<>();
        if (selectCondition.getDeptName()!=null&&!selectCondition.getDeptName().isEmpty()){
            sql.append("AND d.dept_name LIKE ?");
            patten.add("%"+selectCondition.getDeptName()+"%");
        }
        sql.append( "GROUP BY d.id LIMIT ?,?");
        patten.add((selectCondition.getPage()-1)*selectCondition.getLength());
        patten.add(selectCondition.getLength());

        try {
            List<Map<String, Object>> query = qr.query(sql.toString(), new MapListHandler(), patten.toArray());
            return query;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }


    }

    @Override
    public Long selectDeptByConditionCount(SelectDeptByConditionForm selectCondition) {
        StringBuilder sql = new StringBuilder(" SELECT COUNT(*) FROM " +
                " (SELECT d.id,d.dept_name AS deptName,d.tel,d.email,d.`desc`,COUNT(u.id) AS emps " +
                "  FROM bw_zzy_dept d LEFT JOIN bw_zzy_users u ON d.id = u.dept_id AND u.`status`=1 " +
                "  WHERE 1=1 ");
        List<Object> patten = new ArrayList<>();
        if (selectCondition.getDeptName()!=null&&!selectCondition.getDeptName().isEmpty()){
            sql.append("AND d.dept_name LIKE ?");
            patten.add("%"+selectCondition.getDeptName()+"%");
        }
        sql.append( " GROUP BY d.id " +
                "        )AS temp");
        try {
            return qr.query(sql.toString(),new ScalarHandler<>(),patten.toArray());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    @Override
    public Integer insert(Dept insertDept) {
        try {
            int update = qr.update("INSERT INTO bw_zzy_dept SET dept_name=?," +
                    "tel=?,email=?,`desc`=?", insertDept.getDeptName(), insertDept.getTel(),
                    insertDept.getEmail(), insertDept.getDesc());
            return update;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }


    }





    public List<Map<String, Object>> selectById(Integer id) {
        try {
            return qr.query(" SELECT id, dept_name AS deptName, `tel`, `email`, `desc` " +
                    " FROM bw_zzy_dept " +
                    " WHERE id = ? ",new MapListHandler(),id);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Integer update(UpdateDeptForm updateDeptForm) {
        try {
            return qr.update(" UPDATE bw_zzy_dept SET dept_name=?, " +
                    " tel=?,email=?,`desc`=? WHERE id = ? ",updateDeptForm.getDeptName(),updateDeptForm.getTel(),updateDeptForm.getEmail(),updateDeptForm.getDesc(),updateDeptForm.getId());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Integer delete(List<Integer> ids) {
        StringBuilder sql = new StringBuilder("DELETE FROM bw_zzy_dept WHERE id IN (");
        sql.append(ids.stream().map(id -> "?").collect(Collectors.joining(","))).append(")");
        try {
            return qr.update(sql.toString(), ids.toArray());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }


    @Override
    public boolean selectCanDelete(List<Integer> ids) {
        StringBuilder sql = new StringBuilder("SELECT IF(SUM(temp.users) > 0, FALSE, TRUE) AS result FROM (");
        sql.append("SELECT COUNT(u.id) AS users FROM bw_zzy_dept d JOIN bw_zzy_users u ON d.id = u.dept_id WHERE d.id IN (")
                .append(ids.stream().map(id -> "?").collect(Collectors.joining(",")))
                .append(") GROUP BY d.id) temp");
        try {
            Long result = qr.query(sql.toString(), new ScalarHandler<Long>(), ids.toArray());
            return result == 1 ? true : false;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    /**
     * SELECT id, dept_name AS deptName
     * FROM bw_zzy_dept
     * ORDER BY id
     * @return
     */
    @Override
    public List<Map<String, Object>> selectAllDept() {
        try {
            return qr.query("SELECT id, dept_name AS deptName\n" +
                    "FROM bw_zzy_dept\n" +
                    "ORDER BY id",new MapListHandler());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
